package com.googlecode.bip.dao.mysql;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import javax.naming.NamingException;
import org.apache.log4j.Logger;
import com.googlecode.bip.dao.ReferentReferentViewDAO;
import com.googlecode.bip.dao.utils.DBHelper;
import com.googlecode.bip.entity.IReferent;
import com.googlecode.bip.entity.IReferentReferentView;
import com.googlecode.bip.entity.ReferentReferentView;

import cx.ath.choisnet.sql.ConnectionQuery;

/**
 *
 * @author Claude CHOISNET
 */
public class MySQLReferentReferentViewDAO extends ReferentReferentViewDAO
{
    private final static transient Logger logger = Logger.getLogger( MySQLReferentReferentViewDAO.class );

    @Override
    public List<IReferentReferentView> getList( String sql )
        throws SQLException, NamingException
    {
        Connection connection = MySQLDAOFactory.getConnection();

        try {;
            return DBHelper.getList( this, connection, sql );
            }
        finally {
            MySQLDAOFactory.returnConnectionToPool( connection );
            }
    }

    private int doUpdate( String sql )
        throws NamingException, SQLException
    {
        if( logger.isDebugEnabled() ) {
            logger.debug( "doUpdate: " + sql );
            }

        Connection connection = MySQLDAOFactory.getConnection();
        final ConnectionQuery query = new ConnectionQuery( connection );

        try {
            return query.doUpdate( sql );
            }
        finally {
            query.quietClose();
            MySQLDAOFactory.returnConnectionToPool( connection );
            }
    }

    @Override
    public IReferentReferentView findReferent( int idreferent )
        throws NamingException, SQLException
    {
        final String sql = "SELECT * FROM `referentsview` WHERE `idreferent`='"
            + idreferent
            +"';";

        if( logger.isDebugEnabled() ) {
            logger.debug( "findReferent: " + sql );
            }

        Connection connection = MySQLDAOFactory.getConnection();
        final ConnectionQuery query = new ConnectionQuery( connection );

        try {
            ResultSet rSet = query.executeQuery( sql );

            if( rSet.first() ) {
                return newInstance( rSet );
                }
            }
        finally {
            query.quietClose();
            MySQLDAOFactory.returnConnectionToPool( connection );
            }

        return null;
    }

    @Override
    public List<IReferentReferentView> findReferentList()
        throws NamingException, SQLException
    {
        return getList( "SELECT * FROM `referentsview`;" );
    }

    @Override
    public boolean insertReferent( IReferentReferentView ireferent )
        throws NamingException, SQLException
    {
        ReferentReferentView referent = ReferentReferentView.class.cast( ireferent );
        String   sql      = "CALL add_referent( '"
                                + referent.getLoginUserName()
                                + "','"
                                + referent.private_getLoginPassWord()
                                + "','"
                                + referent.getReferentName()
                                + "','"
                                + referent.getReferentDescription()
                                + "');";

        if( logger.isDebugEnabled() ) {
            logger.debug( "insertReferent: " + sql );
            }

        Connection connection = MySQLDAOFactory.getConnection();
        final ConnectionQuery query = new ConnectionQuery( connection );

        try {
            ResultSet rSet = query.executeQuery( sql );

            if( rSet.first() ) {
                referent.set( rSet );

                return true;
                }
            }
        finally {
            query.quietClose();
            MySQLDAOFactory.returnConnectionToPool( connection );
            }

        return false;
    }

    @Override
    public boolean deleteReferent( int idreferent )
        throws NamingException, SQLException
    {
        final String sql = "CALL delete_referent( '"
            + idreferent + "' );";

        doUpdate( sql );

        return true;
    }

    @Override
    public boolean updateReferent( IReferent ireferent )
        throws NamingException, SQLException
    {
        ReferentReferentView referent = ReferentReferentView.class.cast( ireferent );
        String   sql      = "CALL add_referent( '"
            + referent.getIdReferent()
            + "','"
            + referent.getLoginUserName()
            + "','"
            + referent.private_getLoginPassWord()
            + "','"
            + referent.getReferentName()
            + "','"
            + referent.getReferentDescription()
            + "');";

        Connection connection = MySQLDAOFactory.getConnection();
        final ConnectionQuery query = new ConnectionQuery( connection );

        try {
            ResultSet rSet = query.executeQuery( sql );

            if( rSet.first() ) {
                referent.set( rSet );

                return true;
                }
            }
        finally {
            query.quietClose();
            MySQLDAOFactory.returnConnectionToPool( connection );
            }

        return false;
    }

}
